DataExchange - Q&A

 

Are Google Sheets supported by the import?

Two conditions need to be met:

  • The CSV file needs to be accessible.
  • The URL needs to return the CSV-file directly.

Example: https://docs.google.com/u/0/uc?id=%5BHERE_COMES_YOUR_HASH%5D&export=download

As the URL returns the CSV file directly it can be used in a import task.

 

How can I use Google Sheets with multiple tabs/sheets?

To download a specific sheet in the Google Sheet, use the following syntax:

https://docs.google.com/spreadsheets/d/{key}/gviz/tq?tqx=out:csv&sheet={sheet_name}

where:

  • key = the Document ID
  • sheet_name = the name of the sheet to export

 

Can I export Audience List data with a TranslateValue expression?

When the selected Audience List contains a field with an Option List value using the translateValue expression, this export will fail as the translateValue expression is not supported by the export.

 

What to do when data is truncated during import?

The Excel import in JobAgent is one of the few places in which we rely on the MS OLEDB driver to read data. This driver is known to have a limitation that is hard to work around. It will read the first 8 rows, and for any row exceeding 255 characters, a MEMO field is assumed rather than a TEXT field, which would be limited to 255 chars.

This means that if there is large data beyond the first 8 rows, that data is bound to be truncated.

There are several possibilities to tackle this:

Option 1

It is possible to increase the amount of scanned rows beyond 8 through a registry change of HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel.

This 'fix' would require registry changes on production servers.
There would be still a hard limit to the amount of scanned rows - effectively reducing the issue, but not eradicating it.

Option 2

If a specific column is known to be the culprit, the data can be sorted on the size of that column, effectively moving the large data up front.

Knowledge about the data is required to do the proper sorting.
This is only a solution if at most 1 column contains large data.

Option 3

For csv imports, the JobAgent does not rely on the OLEDB driver, but on custom code. So CSV's do not suffer from the truncation problem.

The export will have to be reworked to generate a CSV rather than an Excel.